package com.hzncc.scaffold.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.hzncc.scaffold.entity.Bo.MyCertBo;
import com.hzncc.scaffold.entity.Bo.RecordListBo;
import com.hzncc.scaffold.entity.Other.Certificate;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hzncc.scaffold.entity.Vo.GradesAndCertificatesBo;
import com.hzncc.scaffold.entity.Vo.PaperRecordVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * <p>
 * 证书表 Mapper 接口
 * </p>
 *
 * @author DrunkBear
 * @since 2022-04-27
 */
@Component
public interface CertificateMapper extends BaseMapper<Certificate> {

    @Select("SELECT\n" +
            "\t`course`.*,\n" +
            "\t`chapters`.`id` AS `chaptersId`,\n" +
            "\t`chapters->tasks`.`id` AS `chaptersTasksId`,\n" +
            "\t`chapters->tasks`.`title` AS `paperTitle`,\n" +
            "\t`chapters->tasks`.`examType` AS `chaptersTasksExamType`,\n" +
            "\t`chapters->tasks->paper`.`id` AS `chaptersTasksPaperId`,\n" +
            "\t`chapters->tasks->paper`.`totalScore` AS `totalScore`,\n" +
            "\t`chapters->tasks->paper`.`passScore` AS `passScore`\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`course`.`id`,\n" +
            "\t\t`course`.`title` \n" +
            "\tFROM\n" +
            "\t\t`course` AS `course` \n" +
            "\tWHERE\n" +
            "\t\t`course`.`status` = 1 \n" +
            "\t\tAND `course`.`isDelete` = 0 \n" +
            "\t\tAND (\n" +
            "\t\tSELECT\n" +
            "\t\t\t`chapters`.`courseId` \n" +
            "\t\tFROM\n" +
            "\t\t\t`chapter` AS `chapters`\n" +
            "\t\t\tINNER JOIN `task` AS `tasks` ON `chapters`.`id` = `tasks`.`chapterId` \n" +
            "\t\t\tAND `tasks`.`type` = 3 \n" +
            "\t\tWHERE\n" +
            "\t\t\t( `chapters`.`courseId` = `course`.`id` ) \n" +
            "\t\t\tLIMIT 1 \n" +
            "\t\t) IS NOT NULL \n" +
            "\tORDER BY\n" +
            "\t\t`course`.`id` ASC \n" +
            "\t) AS `course`\n" +
            "\tINNER JOIN `chapter` AS `chapters` ON `course`.`id` = `chapters`.`courseId`\n" +
            "\tINNER JOIN `task` AS `chapters->tasks` ON `chapters`.`id` = `chapters->tasks`.`chapterId` \n" +
            "\tAND `chapters->tasks`.`type` = 3\n" +
            "\tLEFT OUTER JOIN `paper` AS `chapters->tasks->paper` ON `chapters->tasks`.`paperId` = `chapters->tasks->paper`.`id`\n ${ew.customSqlSegment}" +
            "ORDER BY\n" +
            "\t`course`.`id` ASC")
    public List<GradesAndCertificatesBo> ListBo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);




    @Select("SELECT\n" +
            "\t`course`.*,\n" +
            "\t`chapters`.`id` AS `chaptersId`,\n" +
            "\t`chapters->tasks`.`id` AS `chaptersTasksId`,\n" +
            "\t`chapters->tasks`.`title` AS `paperTitle`,\n" +
            "\t`chapters->tasks`.`examType` AS `chaptersTasksExamType`,\n" +
            "\t`chapters->tasks->paper`.`id` AS `chaptersTasksPaperId`,\n" +
            "\t`chapters->tasks->paper`.`totalScore` AS `totalScore`,\n" +
            "\t`chapters->tasks->paper`.`passScore` AS `passScore` \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`course`.`id`,\n" +
            "\t\t`course`.`title` \n" +
            "\tFROM\n" +
            "\t\t`course` AS `course` \n" +
            "\tWHERE\n" +
            "\t\t`course`.`status` = 1 \n" +
            "\t\tAND `course`.`isDelete` = 0 \n" +
            "\t\tAND (\n" +
            "\t\tSELECT\n" +
            "\t\t\t`chapters`.`courseId` \n" +
            "\t\tFROM\n" +
            "\t\t\t`chapter` AS `chapters`\n" +
            "\t\t\tINNER JOIN `task` AS `tasks` ON `chapters`.`id` = `tasks`.`chapterId` \n" +
            "\t\t\tAND `tasks`.`type` = 3 \n" +
            "\t\tWHERE\n" +
            "\t\t\t( `chapters`.`courseId` = `course`.`id` ) \n" +
            "\t\t\tLIMIT 1 \n" +
            "\t\t) IS NOT NULL \n" +
            "\tORDER BY\n" +
            "\t\t`course`.`id` ASC \n" +
            "\t) AS `course`\n" +
            "\tINNER JOIN `chapter` AS `chapters` ON `course`.`id` = `chapters`.`courseId`\n" +
            "\tINNER JOIN `task` AS `chapters->tasks` ON `chapters`.`id` = `chapters->tasks`.`chapterId` \n" +
            "\tAND `chapters->tasks`.`type` = 3\n" +
            "\tLEFT OUTER JOIN `paper` AS `chapters->tasks->paper` ON `chapters->tasks`.`paperId` = `chapters->tasks->paper`.`id`\n " +
            "\tLEFT JOIN coursemajor ON course.id = coursemajor.courseId\n" +
            "" +
            " ${ew.customSqlSegment}" +
            "ORDER BY\n" +
            "\t`course`.`id` DESC")
    public List<GradesAndCertificatesBo> ListBoPlus(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    @Select("SELECT\n" +
            "\tCOUNT( DISTINCT id ) \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t`course`.*,\n" +
            "\t\t`chapters`.`id` AS `chaptersId`,\n" +
            "\t\t`chapters->tasks`.`id` AS `chaptersTasksId`,\n" +
            "\t\t`chapters->tasks`.`title` AS `paperTitle`,\n" +
            "\t\t`chapters->tasks`.`examType` AS `chaptersTasksExamType`,\n" +
            "\t\t`chapters->tasks->paper`.`id` AS `chaptersTasksPaperId`,\n" +
            "\t\t`chapters->tasks->paper`.`totalScore` AS `totalScore`,\n" +
            "\t\t`chapters->tasks->paper`.`passScore` AS `passScore` \n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\t`course`.`id`,\n" +
            "\t\t\t`course`.`title` \n" +
            "\t\tFROM\n" +
            "\t\t\t`course` AS `course` \n" +
            "\t\tWHERE\n" +
            "\t\t\t`course`.`status` = 1 \n" +
            "\t\t\tAND `course`.`isDelete` = 0 \n" +
            "\t\t\tAND (\n" +
            "\t\t\tSELECT\n" +
            "\t\t\t\t`chapters`.`courseId` \n" +
            "\t\t\tFROM\n" +
            "\t\t\t\t`chapter` AS `chapters`\n" +
            "\t\t\t\tINNER JOIN `task` AS `tasks` ON `chapters`.`id` = `tasks`.`chapterId` \n" +
            "\t\t\t\tAND `tasks`.`type` = 3 \n" +
            "\t\t\tWHERE\n" +
            "\t\t\t\t( `chapters`.`courseId` = `course`.`id` ) \n" +
            "\t\t\t\tLIMIT 1 \n" +
            "\t\t\t) IS NOT NULL \n" +
            "\t\tORDER BY\n" +
            "\t\t\t`course`.`id` ASC \n" +
            "\t\t) AS `course`\n" +
            "\t\tINNER JOIN `chapter` AS `chapters` ON `course`.`id` = `chapters`.`courseId`\n" +
            "\t\tINNER JOIN `task` AS `chapters->tasks` ON `chapters`.`id` = `chapters->tasks`.`chapterId` \n" +
            "\t\tAND `chapters->tasks`.`type` = 3\n" +
            "\t\tLEFT OUTER JOIN `paper` AS `chapters->tasks->paper` ON `chapters->tasks`.`paperId` = `chapters->tasks->paper`.`id`\n " +
            "\t\tLEFT JOIN coursemajor ON course.id = coursemajor.courseId \n ${ew.customSqlSegment}" +
            "\tORDER BY\n" +
            "\t`course`.`id` ASC \n" +
            "\t) AS total")
    public Integer selectTotal(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);




    @Select("SELECT\n" +
            "\tpaperrecord.id AS id,\n" +
            "\tpaperrecord.passed AS passed,\n" +
            "\tcertificate.id AS certificate\n" +
            "FROM\n" +
            "\t`paperrecord` \n" +
            "LEFT JOIN certificate ON certificate.recordId = paperrecord.id ${ew.customSqlSegment}")
    public List<PaperRecordVo> listRecord(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    public List<MyCertBo> myCert(Integer userId,Integer type,String keyword);

    public List<MyCertBo> myCertPlus(Integer userId,String keyword);

    @Select(" SELECT\n" +
            "            `certificate`.id,\n" +
            "            `certificate`.certNum,\n" +
            "            `certificate`.recordId,\n" +
            "            `certificate`.userId,\n" +
            "            `certificate`.imgUrl,\n" +
            "            `certificate`.isDelete,\n" +
            "            `certificate`.createTime,\n" +
            "            `certificate`.belongTo\n" +
            "        FROM\n" +
            "            `certificate` AS `certificate`\n" +
            "            INNER JOIN `paperrecord` AS `paperrecord` ON `certificate`.`recordId` = `paperrecord`.`id`\n" +
            "           ${ew.customSqlSegment}")
    public List<Certificate> listAll(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);
}
